﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Utils
{
    public class SqlDataBaseClient : IDataBaseClient
    {
        #region Attributes

        private readonly string _connectionString;

        #endregion

        #region Constants

        private const string CheckTableQuery = "SELECT OBJECT_ID ('[{0}]','U')";

        #endregion

        #region Public Methods

        public SqlDataBaseClient(string connectionString)
        {
            _connectionString = connectionString;
        }

        public DataTable ExecuteQuery(string query)
        {
            return this.ExecuteQuery(query, 30, null, null);
        }

        public DataTable ExecuteQuery(string query, string[] parameterNames, object[] parameterValues)
        {
            return this.ExecuteQuery(query, 30, parameterNames, parameterValues);
        }

        public DataTable ExecuteQuery(string query, int timeout, string[] parameterNames, object[] parameterValues)
        {
            DataTable result = new DataTable();

            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        connection.Open();
                        command.CommandTimeout = timeout;

                        // Add parameters
                        if (parameterNames != null)
                        {
                            for (int parameterIndex = 0; parameterIndex < parameterNames.Length; parameterIndex++)
                            {
                                command.Parameters.AddWithValue(parameterNames[parameterIndex], parameterValues[parameterIndex]);
                            }
                        }

                        //using (SqlDataReader reader = command.ExecuteReader())
	                    {
		                    SqlDataReader reader = command.ExecuteReader();
							// Create the columns
							for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
							{
								Type fieldType = reader.GetFieldType(columnIndex);
								if (fieldType == null)
								{
									throw new DataException("The table contains an UDT type that is not present at the application");
								}
								result.Columns.Add(reader.GetName(columnIndex), fieldType);
							}

							// Fill them
							while (reader.Read())
							{
								object[] auxRow = new object[reader.FieldCount];
								reader.GetValues(auxRow);
								result.LoadDataRow(auxRow, true);
							}
                        }

                    }
                }
			}
            catch (Exception ex)
            {
                ex.Data.Add("Query",query);
                throw;
            }

            return result;
        }

        public object ExecuteScalar(string query)
        {
            return this.ExecuteScalar(query, 30, null, null);
        }

        public object ExecuteScalar(string query, string[] parameterNames, object[] parameterValues)
        {
            return this.ExecuteScalar(query, 30, parameterNames, parameterValues);
        }

        public object ExecuteScalar(string query, int timeout, string[] parameterNames, object[] parameterValues)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    connection.Open();
                    command.CommandTimeout = timeout;

                    // Add parameters
                    if (parameterNames != null)
                    {
                        for (int parameterIndex = 0; parameterIndex < parameterNames.Length; parameterIndex++)
                        {
                            command.Parameters.AddWithValue(parameterNames[parameterIndex],
                                                            parameterValues[parameterIndex]);
                        }
                    }

                    return command.ExecuteScalar();
                }
            }
        }

        public int ExecuteCommand(string query)
        {
            return this.ExecuteCommand(query, 30, null, null);
        }

        public int ExecuteCommand(string query, string[] parameterNames, object[] parameterValues)
        {
            return this.ExecuteCommand(query, 30, parameterNames, parameterValues);
        }

        public int ExecuteCommand(string query, int timeout, string[] parameterNames, object[] parameterValues)
        {
			try
			{
				using (SqlConnection connection = new SqlConnection(_connectionString))
				{
					using (SqlCommand command = new SqlCommand(query, connection))
					{
						connection.Open();
						command.CommandTimeout = timeout;

						// Add parameters
						if (parameterNames != null)
						{
							for (int parameterIndex = 0; parameterIndex < parameterNames.Length; parameterIndex++)
							{
								command.Parameters.AddWithValue(parameterNames[parameterIndex],
								                                parameterValues[parameterIndex]);
							}
						}

						return command.ExecuteNonQuery();
					}
				}
			}
			catch (Exception ex)
			{
				ex.Data.Add("Query", query);
				throw;
			}
        }

        public bool ExistsTable(string name)
        {
            object result = this.ExecuteScalar(string.Format(CheckTableQuery, name));
            return !(result is DBNull);
        }
 
        #endregion

    }
}
